<?php

//error_reporting(E_ERROR);
class DbOperation
{
    private static $oper;
    private $conn;
    private $dbname;
    private $prefix;

    /**
     * chu
     * @return DbOperation
     */
    public static function getInstance()
    {
        if (!(self::$oper instanceof self)) {
            self::$oper = new self();
        }
        return self::$oper;
    }

    /**
     *  初始化数据库连接
     * @return mysqli
     */
    private function __construct()
    {
        include $_SERVER['DOCUMENT_ROOT'] . "/config/dbconfig.php";
        $this->dbname = $dbinfo["dbname"];
        $this->prefix = $dbinfo["dbprefix"];
        $this->conn = mysqli_connect($dbinfo["dbhost"], $dbinfo["dbuser"], $dbinfo["dbpass"], $dbinfo["dbname"], $dbinfo["dbport"]);
        if (!$this->conn) {
            die("连接数据库失败,请检查数据库连接信息");
        }
        $this->conn->set_charset($dbinfo["charset"]);
    }

    /**获取表前缀
     * @return mixed
     */
    function getPrefix()
    {
        return $this->prefix;
    }

    /**
     * 获取一条数据
     * @param $sql
     * @return mixed
     */
    function selectOne($sql)
    {
        $temp = "$sql limit 1 ";
        error_log($temp);
        $result = $this->conn->query($temp);
        return $this->operResult($result)[0];
    }

    private function operResult($result)
    {
        $redata = array();
        if (empty($result)) {
            return $result;
        }
        if (is_bool($result) || is_int($result)) {
            return $result;
        }
        /**
         * fetch_array  通过a['col'] 方式取数据
         * fetch_object 通过a->col 方式取数据
         */
        while ($row = $result->fetch_array()) {
//             $temp_data=array_change_key_case($row);
            array_push($redata, $this->convertKey($row));
        }
//        mysqli_free_result($result);
        return $redata;
    }

    /**
     * @param $row
     * @return array
     */
    private function convertKey($row)
    {
        $redata = array();
        foreach ($row as $key => $value) {
            if (is_int($key)) {
                continue;
            }
//            error_log($key."---".$value);
            $redata[$this->toCamelCase($key)] = $value;
        }
//        error_log(json_encode($redata));
        return $redata;
    }

    /**下划线命名到驼峰命名
     * @param $str
     * @return mixed|string
     */
    function toCamelCase($str)
    {
        $array = explode('_', $str);
        $result = $array[0];
        $len = count($array);
        if ($len > 1) {
            for ($i = 1; $i < $len; $i++) {
                $result .= ucfirst($array[$i]);
            }
        }
        return $result;
    }

    /**
     * 获取所有数据
     * @param $sql
     * @return mixed
     */
    function selectList($sql)
    {
        error_log($sql);
        $result = $this->conn->query($sql);
        return $this->operResult($result);
    }

    /**
     * 获取字段信息
     * @param $tb_name
     */
    function tableFieldsInfo($tb_name)
    {
        $sql = "select column_name,column_default,is_nullable,data_type,character_maximum_length," .
            "character_octet_length,column_key,	extra from information_schema.columns where table_name='" .
            $tb_name . "' order by  ordinal_position ";
        return $this->execute($sql);
    }

    /**
     * 分页查询数据
     * @param $sql
     * @param int $pageNo
     * @param int $pageSize
     * @return mixed
     */
    function pageList($sql, $pageNo = 0, $pageSize = 20)
    {
        $pageSize = $pageSize <= 0 ? 20 : $pageSize;
        $pageNo = ($pageNo - 1) < 0 ? 0 : $pageNo - 1;
        $temp = $sql . " limit " . $pageNo * $pageSize . ",$pageSize";
        error_log($temp);
        $result = $this->conn->query($temp);
        return $this->operResult($result);
    }

    function count($sql)
    {
        $esql = "select count(1) as total $sql";
        error_log($esql);
        $result = $this->execute($esql);

        return $result[0]["total"];
    }

    /**
     * 执行插入,修改,删除 sql
     * @param $sql
     * @return bool|mysqli_result
     */
    function execute($sql)
    {
        error_log($sql);
        $result = $this->conn->query($sql);
        return $this->operResult($result);
    }

    /**
     * 数据库备份
     * @param string $backup_dir 备份目录
     * @param int $type 1 全量备份 2 备份表结构 3 备份数据
     * @return string
     */
    function backup($backup_dir = "backup", $type = 1)
    {
        $file_name = time() . ".sql";
        //备份表
        $sql = "select table_name,table_comment from information_schema.tables where table_schema='"
            . $this->dbname . "' and table_type='BASE TABLE' and table_name like '"
            . $this->prefix . "%'";
        $bk_tables = $this->execute($sql);
        // error_log(json_encode($bk_tables));
        if ($type === 1 || $type === 2) {
            $file = fopen($backup_dir . "/table_bake$file_name", "w+") or die("创建表结构备份文件失败");
            foreach ($bk_tables as $key => $val) {
                //生成表结构
                $sql = "show create table " . $val["TABLENAME"];
                fwrite($file, "drop table if exists " . $val["TABLENAME"] . ";\n");
                // error_log("drop table if exists " . $val["TABLENAME"] . ";\n");
                $data = $this->execute($sql);
                //error_log(json_encode($table_fileds));
                fwrite($file, $data[0]["Create Table"] . ";\n");
                // error_log($data[0]["Create Table"] . ";\n");
            }
            fclose($file);
        }
        if ($type === 1 || $type === 3) {
            $data_file = fopen($backup_dir . "/data_bake$file_name", "w+") or die("创建数据备份文件失败");
            //生成数据
            foreach ($bk_tables as $key => $val) {
                $sql = "select column_name ,data_type from information_schema.columns where table_name='"
                    . $val["TABLENAME"] . "'";
                $columns = $this->execute($sql);
                $sql = "select * from " . $val["TABLENAME"];
                $result = $this->conn->query($sql);
                while ($row = $result->fetch_array()) {
                    $isql = $this->createInsertSql($val["TABLENAME"], $columns, $row);
                    fwrite($data_file, $isql);
                    //  error_log($this->createInsertSql($val["TABLENAME"], $columns, $row));
                }
            }
            fclose($data_file);
        }

        return $file_name;
        //备份数据
    }

    /**
     * 生成插入sql语句
     * @param $tb_name 表名
     * @param $columns 列明
     * @param $row 数据
     * @return string
     */
    private function createInsertSql($tb_name, $columns, $row)
    {
        $sql = "insert into $tb_name";
        $fields = "";
        $data = "";
        foreach ($columns as $key => $val) {
            $col = $val["COLUMNNAME"];
            $fields .= $col . ",";
            $temp_val = empty($row[$col]) ? 0 : $row[$col];
            $data .= $val["DATATYPE"] == "int" ? $temp_val . "," : "'" . $temp_val . "',";
        }
        $fields = substr($fields, 0, strlen($fields) - 1);
        $data = substr($data, 0, strlen($data) - 1);
        //sql 注释 # 或者--  双横线 需要有个空格
        $sql .= "(" . $fields . ")values(" . $data . ");-- split line---\n";
        return $sql;
    }

    /**
     * 还原指定sql脚本
     * @param $bkname
     * @param int $type 1 还原表结构和数据 2 只还原数据
     */
    function restore($bkname, $type = 1)
    {
        if ($type === 1) {
            $name = $_SERVER["DOCUMENT_ROOT"] . "/backup/table_bake$bkname";
            $ftable = fopen($name, "r") or die("打开表结构文件失败");
            $table_data = fread($ftable, filesize($name));
            $tarr = explode(";", $table_data);
            foreach ($tarr as $key => $val) {
                $this->conn->query($val);
            }
            //  error_log($table_data);
            fclose($ftable);
        }
        $dname = $_SERVER["DOCUMENT_ROOT"] . "/backup/data_bake$bkname";
        $fdata = fopen($dname, "r") or die("打开数据文件失败");
        $data = fread($fdata, filesize($dname));
        $dataarr = explode(";-- split line---", $data);
        foreach ($dataarr as $key => $val) {
            $this->conn->query($val);
        }
        fclose($fdata);
        // error_log($data);
    }

}